﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
namespace InspectAll
{
    class Class2
    {

        public void WriteDataToDB(DataSet ds)
        {
            using (SqlConnection conn = new SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog =Test"))
            {
                conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter("select * from InspectAll", conn);

                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                DataTable dt = ds.Tables[0];
                
               
                foreach (DataRow item in dt.Rows)
                {
                    item.SetAdded();
                }

                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.Update(ds,"[Sheet1$]");
            }
        }
        public DataSet ReadDataFromExcel()
        {
            string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =C:\\test.xls; Extended Properties='Excel 8.0; HDR=Yes'";
            DataSet ds;
            OleDbConnection conn = new OleDbConnection(strCon);
            String sql = "Select * from [Sheet1$]";
            conn.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, strCon);
            ds = new DataSet();
            adapter.Fill(ds, "[Sheet1$]");
            conn.Close();
            return ds;
        }
    }
}
